package srdb.migration.dbhandler.enterprisedb;

import com.edb.commons.db.DBUtils;
import com.edb.jdbc2.AbstractJdbc2Connection;

import java.sql.Array;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import srdb.migration.common.IMTKConnection;
import srdb.migration.schema.metadata.Function;
import srdb.migration.schema.metadata.Package;
import srdb.migration.schema.metadata.Procedure;
import srdb.migration.schema.metadata.Schema;
import srdb.migration.schema.metadata.Synonym;
import srdb.migration.schema.metadata.Trigger;

public class MetaData extends srdb.migration.dbhandler.srdb.MetaData {
	public MetaData(IMTKConnection conn) throws Exception {
		super(conn);
	}

	public MetaData(IMTKConnection conn, String targetSchemaName)
			throws Exception {
		super(conn, targetSchemaName);
	}

	protected void initProcStatement(String commaSepProcNames)
			throws SQLException {
		String prosigCol = "'' AS prosig";
		String defVals = "sr_get_expr(proargdefaults,'sr_proc'::regclass) as defvals";
		if (this.conn.getConnection().getMetaData().getDatabaseProductVersion()
				.compareTo("9.0") < 0) {
			prosigCol = "prosig";
			defVals = "'' AS defvals";
		}
		this.getProcsStr = ("select  nspname,proname, pronargs,proargtypes,proargmodes,proargnames,"
				+ prosigCol
				+ ","
				+ defVals
				+ ",prosrc,"
				+ "typ.typname as rettypename, lan.lanname,proargmodes, proallargtypes, proretset, protype, provolatile,probin,proisstrict from sr_proc proc "
				+ "JOIN sr_language lan on lan.oid = proc.prolang "
				+ "  JOIN sr_type typ on typ.oid = proc.prorettype "
				+ "  JOIN sr_namespace nsp on nsp.oid=pronamespace "
				+ "  WHERE nspname = ? " + "  and typ.typname <> 'trigger' and protype=? and lanname not in ('internal') ");

		if ((commaSepProcNames != null)
				&& (commaSepProcNames.trim().length() > 0)) {
			this.getProcsStr = (this.getProcsStr + "AND proname IN ("
					+ commaSepProcNames + ")");
		}

		this.getProcsStr += " ORDER BY proc.oid";
		this.getProcs = this.conn.getConnection().prepareStatement(
				this.getProcsStr);
	}

	protected void getProcedures() throws SQLException {
		this.getProcs.setString(1, this.schema.getName());
		this.getProcs.setInt(2, 1);
		ResultSet rs = this.getProcs.executeQuery();
		try {
			while (rs.next()) {
				String sql = "";
				String language = rs.getString("lanname");
				String prosig = rs.getString("prosig");
				String prosrc = rs.getString("prosrc");
				if ((prosig == null) || (prosig.trim().length() == 0)) {
					sql = getFullProSource(
							true,
							rs.getString("proname"),
							null,
							false,
							rs.getArray("proargnames"),
							rs.getString("proargtypes"),
							rs.getString("proallargtypes") == null ? null : rs
									.getArray("proallargtypes"),
							rs.getArray("proargmodes"),
							rs.getString("defvals"), prosrc, language, null,
							rs.getBoolean("proisstrict"),
							rs.getString("probin"));
				} else {
					sql = "CREATE OR REPLACE PROCEDURE "
							+ rs.getString("proname");

					String idPtrn = "(([_a-zA-Z][_0-9a-zA-Z]*)|(['][^']*['])+|([\"][^\"]*[\"])+)";
					String regEx = "\\s*CREATE\\s+(OR\\s+REPLACE\\s+)?PROCEDURE\\s+("
							+ idPtrn + "([.]" + idPtrn + ")*)(.*)";
					Pattern p = Pattern.compile(regEx, 10);
					Matcher m = p.matcher(prosig.replace('\n', ' '));
					if (m.matches())
						sql = sql + m.group(m.groupCount());
					else {
						throw new SQLException("Unhandled SQL signature");
					}

					sql = sql.trim() + "\n" + prosrc;
				}

				if ((sql != null) && (sql.trim().length() > 0)) {
					Procedure p = new Procedure(rs.getString("proname"),
							this.schema.getName(), true);
					p.setSQL(sql);
					this.schema.addProcedure(p);
				}
			}
		} finally {
			closeResultSet(rs);
		}
	}

	private String getFullProSource(boolean isProc, String proname,
			String rettypename, boolean proretset, Array proargnames,
			String proargtypes, Array proallargtypes, Array proargmodes,
			String defvals, String prosrc, String language,
			String volatileAttribute, boolean proisstrict, String probin)
			throws SQLException {
		PreparedStatement typeStmt = this.conn
				.getConnection()
				.prepareStatement(
						"SELECT sr_catalog.format_type(sr_type.oid, typtypmod) AS type_name      FROM sr_catalog.sr_type      JOIN sr_catalog.sr_namespace ON (sr_namespace.oid = typnamespace)      WHERE sr_type.oid = ?");

		String funcSql = "CREATE OR REPLACE "
				+ (isProc ? "PROCEDURE" : "FUNCTION") + " ";
		funcSql = funcSql + proname;
		String returnType = rettypename;
		if ((!isProc) && (proretset)) {
			returnType = "SETOF " + returnType;
		}

		String[] argnames = new String[0];

		if (proargnames != null) {
			argnames = (String[]) (String[]) proargnames.getArray();
		}

		String proArgTypesStr = proargtypes;
		String[] argTypes = (proArgTypesStr == null)
				|| (proArgTypesStr.trim().length() == 0) ? new String[0]
				: proArgTypesStr.split("\\s+");

		if (proallargtypes != null) {
			Long[] array = (Long[]) (Long[]) proallargtypes.getArray();
			argTypes = new String[array.length];
			for (int i = 0; i < argTypes.length; i++) {
				argTypes[i] = array[i].toString();
			}
		}

		Array modesArray = proargmodes;
		String[] modes = null;

		if (modesArray != null) {
			modes = (String[]) (String[]) modesArray.getArray();
		}

		if (modes == null) {
			modes = new String[0];
		}

		String[] defauls = null;
		if ((defvals != null) && (defvals.trim().length() > 0)) {
			defauls = DBUtils.split(defvals, ',', '\'');
		}

		int size = argTypes.length;
		if ((argTypes.length == 1) && (argTypes[0].equals(""))) {
			size = 0;
		}

		if (size > 0) {
			funcSql = funcSql + "(";
			for (int i = 0; i < argTypes.length; i++) {
				String defval = (defauls != null) && (defauls.length > i)
						&& (defauls[i] != null) && (defauls[i].length() > 0)
						&& (!defauls[i].trim().equals("-")) ? " default "
						+ defauls[i] : "";

				if (i > 0) {
					funcSql = funcSql + ", ";
				}
				typeStmt.setInt(1, Integer.parseInt(argTypes[i]));
				ResultSet typeRs = typeStmt.executeQuery();
				String type = "";
				if (typeRs.next())
					type = typeRs.getString(1);
				else {
					throw new SQLException("Unable to identify argument types");
				}
				typeRs.close();
				String mode = "";
				if (modes.length > i) {
					switch (modes[i].charAt(0)) {
					case 'i':
						mode = "IN";
						break;
					case 'o':
						mode = "OUT";
						break;
					case 'b':
						mode = "INOUT";
					}

				}

				if ((argnames.length > i) && (argnames[i] != null)) {
					funcSql = funcSql + argnames[i] + " ";
				}

				funcSql = funcSql + mode + " " + type + defval;
			}

			funcSql = funcSql + ")";
		} else if (!isProc) {
			funcSql = funcSql + "()";
		}

		if ((!isProc) && (language.equalsIgnoreCase("c"))) {
			boolean isStrict = proisstrict;

			funcSql = funcSql + "\nRETURNS " + returnType + " AS " + "'"
					+ probin + "','" + prosrc + "' LANGUAGE " + language;

			if (isStrict) {
				funcSql = funcSql + " STRICT";
			}
		} else if (!isProc) {
			funcSql = funcSql + "\nRETURNS " + returnType + " AS $$ " + prosrc
					+ " $$ LANGUAGE '" + language + "' ";

			funcSql = funcSql + volatileAttribute + " ";
		} else {
			funcSql = funcSql + "\nAS\n" + prosrc.trim();
		}

		if (!funcSql.trim().endsWith(";")) {
			funcSql = funcSql + ";";
		}

		return funcSql;
	}

	protected void getFunctions() throws SQLException {
		this.getProcs.setString(1, this.schema.getName());
		this.getProcs.setInt(2, 0);
		ResultSet rs = this.getProcs.executeQuery();
		try {
			while (rs.next()) {
				String sql = "";
				String language = rs.getString("lanname");
				String prosig = rs.getString("prosig");
				String prosrc = rs.getString("prosrc");
				String probin = rs.getString("probin");

				String volatileAttribute = rs.getString("provolatile");

				if (volatileAttribute.equalsIgnoreCase("i"))
					volatileAttribute = "immutable";
				else if (volatileAttribute.equalsIgnoreCase("s"))
					volatileAttribute = "stable";
				else {
					volatileAttribute = "volatile";
				}

				if ((prosig == null) || (prosig.trim().length() == 0)) {
					sql = getFullProSource(
							false,
							rs.getString("proname"),
							rs.getString("rettypename"),
							rs.getBoolean("proretset"),
							rs.getArray("proargnames"),
							rs.getString("proargtypes"),
							rs.getString("proallargtypes") == null ? null : rs
									.getArray("proallargtypes"),
							rs.getArray("proargmodes"),
							rs.getString("defvals"), prosrc, language,
							volatileAttribute, rs.getBoolean("proisstrict"),
							probin);
				} else {
					sql = "CREATE OR REPLACE FUNCTION "
							+ rs.getString("proname");

					String idPtrn = "(([_a-zA-Z][_0-9a-zA-Z]*)|(['][^']*['])+|([\"][^\"]*[\"])+)";
					String regEx = "\\s*CREATE\\s+(OR\\s+REPLACE\\s+)?FUNCTION\\s+("
							+ idPtrn + "([.]" + idPtrn + ")*)(.*)";
					Pattern p = Pattern.compile(regEx, 10);
					Matcher m = p.matcher(prosig.replace('\n', ' '));
					String funcNameWithArgs = null;

					if (m.matches()) {
						sql = sql + m.group(m.groupCount());

						String[] tempArr = m.group(m.groupCount()).split(
								"RETURN");

						if ((tempArr != null) && (tempArr[0].length() > 0)) {
							funcNameWithArgs = rs.getString("proname")
									+ tempArr[0];

							funcNameWithArgs = funcNameWithArgs.replace("OUT",
									"");
						}
					} else {
						throw new SQLException("Unhandled SQL signature");
					}

					sql = sql.trim() + "\n" + prosrc;

					if ((!volatileAttribute.equalsIgnoreCase("volatile"))
							&& (funcNameWithArgs != null)) {
						sql = sql + ";\nALTER FUNCTION " + funcNameWithArgs
								+ " " + volatileAttribute + ";\n";
					}
				}

				if ((sql != null) && (sql.trim().length() > 0)) {
					Function f = new Function(rs.getString("proname"),
							this.schema.getName());
					f.setSQL(sql);
					this.schema.addFunction(f);
				}
			}
		} finally {
			closeResultSet(rs);
		}
	}

	protected void initAllPrivateSynonymStatement() throws SQLException {
		boolean isEDB84 = DBUtils.isEDB84OrGreater(this.dbConn);
		if (isEDB84) {
			this.getAllPrivateSynonymStr = "select synname, nsp.nspname as synnamespace, sr_get_userbyid(synowner) as synowner, synobjschema,synobjname, synlink  from sr_synonym s LEFT OUTER JOIN sr_namespace nsp on nsp.oid=s.synnamespace WHERE nsp.nspname = ? ";
		} else {
			this.getAllPrivateSynonymStr = "select synname, sr_get_userbyid(synowner) as synowner, synobjschema,synobjname  from sr_synonym s ";
		}

		if (isEDB84)
			this.getAllPrivateSynonymStr += " ORDER BY s.oid";
		else {
			this.getAllPrivateSynonymStr += " ORDER BY synname";
		}
		this.getAllPrivateSynonyms = this.conn.getConnection()
				.prepareStatement(this.getAllPrivateSynonymStr);
	}

	protected void initAllPublicSynonymStatement() throws SQLException {
		boolean isEDB84 = DBUtils.isEDB84OrGreater(this.dbConn);
		if (isEDB84) {
			this.getAllPublicSynonymStr = "select synname, nsp.nspname as synnamespace, sr_get_userbyid(synowner) as synowner, synobjschema,synobjname, synlink  from sr_synonym s LEFT OUTER JOIN sr_namespace nsp on nsp.oid=s.synnamespace WHERE nsp.nspname = 'public' ";
		} else {
			this.getAllPublicSynonymStr = "select synname, sr_get_userbyid(synowner) as synowner, synobjschema,synobjname  from sr_synonym s ";
		}

		if (isEDB84)
			this.getAllPublicSynonymStr += " ORDER BY s.oid";
		else {
			this.getAllPublicSynonymStr += " ORDER BY synname";
		}
		this.getAllPublicSynonyms = this.conn.getConnection().prepareStatement(
				this.getAllPublicSynonymStr);
	}

	protected void initSynonymStatement(String commaSepSynNames)
			throws SQLException {
		boolean isEDB84 = DBUtils.isEDB84OrGreater(this.dbConn);
		if (isEDB84) {
			this.getSynonymStr = "select synname, nsp.nspname as synnamespace, sr_get_userbyid(synowner) as synowner, synobjschema,synobjname, synlink  from sr_synonym s LEFT OUTER JOIN sr_namespace nsp on nsp.oid=s.synnamespace WHERE nsp.nspname = ? ";
		} else {
			this.getSynonymStr = "select synname, sr_get_userbyid(synowner) as synowner, synobjschema,synobjname  from sr_synonym s ";
		}

		if ((commaSepSynNames != null)
				&& (commaSepSynNames.trim().length() > 0)) {
			this.getSynonymStr = (this.getSynonymStr + " AND synname IN ("
					+ commaSepSynNames + ")");
		}

		if (isEDB84)
			this.getSynonymStr += " ORDER BY s.oid";
		else {
			this.getSynonymStr += " ORDER BY synname";
		}
		this.getSynonyms = this.conn.getConnection().prepareStatement(
				this.getSynonymStr);
	}

	protected void getPublicSynonyms() throws SQLException {
		boolean isEDB84 = DBUtils.isEDB84OrGreater(this.dbConn);
		if (isEDB84) {
			this.getSynonyms.setString(1, this.schema.getName());
		}
		ResultSet rs = this.getSynonyms.executeQuery();
		try {
			while (rs.next()) {
				Synonym syn = new Synonym(this.schema.getName());
				if (isEDB84) {
					syn.setDblink(rs.getString("synlink"));
				}
				syn.setSynname(rs.getString("synname"));
				syn.setSynobjname(rs.getString("synobjname"));
				syn.setSynowner(rs.getString("synowner"));
				syn.setSynObjSchema(rs.getString("synobjschema"));
				this.schema.addSynonym(syn);
			}
		} finally {
			closeResultSet(rs);
		}
	}

	protected void getAllPublicSynonyms() throws SQLException {
		boolean isEDB84 = DBUtils.isEDB84OrGreater(this.dbConn);

		ResultSet rs = this.getAllPublicSynonyms.executeQuery();
		try {
			while (rs.next()) {
				Synonym syn = new Synonym(this.schema.getName());
				if (isEDB84) {
					syn.setDblink(rs.getString("synlink"));
				}
				syn.setSynname(rs.getString("synname"));
				syn.setSynobjname(rs.getString("synobjname"));
				syn.setSynowner(rs.getString("synowner"));
				syn.setSynObjSchema(rs.getString("synobjschema"));
				this.schema.addSynonym(syn);
			}
		} finally {
			closeResultSet(rs);
		}
	}

	protected void getAllPrivateSynonyms() throws SQLException {
		boolean isEDB84 = DBUtils.isEDB84OrGreater(this.dbConn);
		if (isEDB84) {
			this.getAllPrivateSynonyms.setString(1, this.schema.getName());
		}
		ResultSet rs = this.getAllPrivateSynonyms.executeQuery();
		try {
			while (rs.next()) {
				Synonym syn = new Synonym(this.schema.getName());
				if (isEDB84) {
					syn.setDblink(rs.getString("synlink"));
				}
				syn.setSynname(rs.getString("synname"));
				syn.setSynobjname(rs.getString("synobjname"));
				syn.setSynowner(rs.getString("synowner"));
				syn.setSynObjSchema(rs.getString("synobjschema"));
				this.schema.addSynonym(syn);
			}
		} finally {
			closeResultSet(rs);
		}
	}

	protected void initTriggerStatement(String commaSepTableNames)
			throws SQLException {
		this.getTriggersStr = "select pr.proname, pr.prosrc, lan.lanname,tg.tgname, tg.tgtype, c.relname, sr_get_userbyid(pr.proowner) as OWNER, sr_get_userbyid(c.relowner) as TABLE_OWNER,provolatile  from sr_proc  pr  JOIN sr_trigger tg ON tg.tgfoid = pr.oid  JOIN sr_language lan on lan.oid = pr.prolang  LEFT OUTER JOIN sr_class c on tg.tgrelid = c.oid  LEFT OUTER JOIN sr_namespace nsp on nsp.oid=c.relnamespace  WHERE nsp.nspname = ?";

		if ((commaSepTableNames != null)
				&& (commaSepTableNames.trim().length() > 0)) {
			this.getTriggersStr = (this.getTriggersStr + " AND c.relname in ("
					+ commaSepTableNames + ")");
		}

		this.getTriggersStr += " ORDER BY tg.oid";

		this.getTriggers = this.conn.getConnection().prepareStatement(
				this.getTriggersStr);
	}

	protected void getTriggers() throws SQLException {
		this.getTriggers.setString(1, this.schema.getName());
		ResultSet rs = this.getTriggers.executeQuery();
		try {
			while (rs.next()) {
				String replace = "\tIF DELETING = TRUE THEN RETURN OLD;\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tELSE RETURN NEW; END IF;";

				String tgProcSrc = "";
				String[] triggerCond = super.getTriggerCondition(rs
						.getInt("tgtype"));
				String languageName = rs.getString("lanname");
				String sql = "";
				if (languageName.equalsIgnoreCase("edbspl")) {
					tgProcSrc = rs.getString("prosrc");
					tgProcSrc = tgProcSrc.replaceAll(replace, "");
					if (tgProcSrc.contains("$__EDBwrapped__$")) {
						continue;
					}
					sql = "CREATE OR REPLACE TRIGGER "
							+ DBUtils.quoteIdentifier(rs.getString("tgname"))
							+ "\n" + triggerCond[0] + " " + triggerCond[1]
							+ "\n" + "ON "
							+ DBUtils.quoteIdentifier(rs.getString("relname"))
							+ triggerCond[2] + "\n" + tgProcSrc;
				} else if (languageName.equalsIgnoreCase("plpgsql")) {
					String fSql = "CREATE OR REPLACE FUNCTION "
							+ rs.getString("proname")
							+ "() returns trigger as $$\n"
							+ rs.getString("prosrc")
							+ "$$\n language 'plpgsql'";

					sql = fSql + ";\n" + "CREATE TRIGGER "
							+ DBUtils.quoteIdentifier(rs.getString("tgname"))
							+ "\n" + triggerCond[0] + " " + triggerCond[1]
							+ "\n" + "ON "
							+ DBUtils.quoteIdentifier(rs.getString("relname"))
							+ triggerCond[2] + " \n EXECUTE PROCEDURE "
							+ rs.getString("proname") + "();";
				}

				if ((sql != null) && (sql.length() > 0)) {
					Trigger tr = new Trigger(rs.getString("tgname"));
					tr.setBody(sql);
					tr.setOwnerName(rs.getString("OWNER"));
					tr.setTableOwnerName(rs.getString("TABLE_OWNER"));
					this.schema.addTrigger(tr);
				}
			}
		} finally {
			closeResultSet(rs);
		}
	}

	protected void initPackageStatement(String commaSepPackageNames)
			throws SQLException {
		this.getPackagesStr = "select pkgname, pkgheadsrc, pkgbodysrc from edb_package p JOIN sr_namespace ns ON ns.oid=p.pkgnamespace WHERE ns.nspname=? ";

		if ((commaSepPackageNames != null)
				&& (commaSepPackageNames.trim().length() > 0)) {
			this.getPackagesStr = (this.getPackagesStr + " AND pkgname IN ("
					+ commaSepPackageNames + ")");
		}
		this.getPackages = this.conn.getConnection().prepareStatement(
				this.getPackagesStr);
	}

	@Override
	public String getParseTablesMsg() throws SQLException {
		return null;
	}

	protected void getPackages() throws SQLException {
		this.getPackages.setString(1, this.schema.getName());
		ResultSet rs = this.getPackages.executeQuery();
		try {
			while (rs.next()) {
				String body = rs.getString("pkgbodysrc");
				if ((body != null)
						&& (body.trim().startsWith("$__EDBwrapped__$"))) {
					continue;
				}
				Package p = new Package(rs.getString("pkgname"),
						this.schema.getName());
				p.setHeaderSQL(rs.getString("pkgheadsrc"));
				p.setBodySQL(body);
				this.schema.addPackage(p);
			}
		} finally {
			closeResultSet(rs);
		}
	}

	public String[] getSchemas() throws SQLException {
		String excludes = "'sys','dbo','information_schema','pgagent','dbms_job_procedure'";

		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = this.conn.getConnection().createStatement();
			rs = stmt
					.executeQuery("select nspname from sr_namespace where nspname not in ("
							+ excludes
							+ ") and NOT nspname like 'sr__%' ESCAPE '_' AND nspparent=0 order by oid");

			ArrayList list = new ArrayList();

			while (rs.next()) {
				list.add(rs.getString(1));
			}
			String[] arrayOfString;
			arrayOfString = (String[]) list.toArray(new String[0]);
			return arrayOfString;
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (stmt != null)
				stmt.close();
		}
	}

	public boolean isDBLinkOraInstalled() {// N/A
		Statement stmt = null;
		ResultSet rs = null;
		int i;
		try {
			stmt = this.dbConn.createStatement();
			rs = stmt
					.executeQuery("SELECT COUNT(a.oid) FROM sr_proc a WHERE a.proname IN ('dblink_ora_connect', 'dblink_ora_copy', 'dblink_ora_disconnect')");

			int count = 0;

			if (rs.next()) {
				count = rs.getInt(1);

			}
			i = count >= 3 ? 1 : 0;

			// return i;
			if (i == 1) {
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}

				if (stmt != null)
					stmt.close();
			} catch (SQLException ex) {
			}
		}
		return false;
	}

	public int getDBServerMajorVersion() throws SQLException {
		return ((AbstractJdbc2Connection) this.conn.getConnection())
				.getServerMajorVersion();
	}

	public int getDBServerMinorVersion() throws SQLException {
		return ((AbstractJdbc2Connection) this.conn.getConnection())
				.getServerMinorVersion();
	}
}

/*

 */